package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import entity.Goods;
import entity.User;
import utils.JDBCUtil;
import utils.JdbcUtils;

public class UserDao {

	// {1}增加/注册
	public void addUser(User user) {
		// 1. 获取MySQL连接
		Connection connection = null;
		// 2. 编写sql语句
		String sql = "insert into user(uid,uname,pwd)values(?,?,?)";
		PreparedStatement preparedStatement = null;
		// 3. 使用Connection创建PreparedStatement 对象
		try {
			connection = JDBCUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			// 4.给占位符 ? 赋值
			preparedStatement.setInt(1, user.getUid());
			preparedStatement.setString(2, user.getUname());
			preparedStatement.setString(3, user.getPwd());

			// 5.执行SQL语句
			int i = preparedStatement.executeUpdate();
			System.out.println("已添加" + i + "个用户");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.关闭资源
			JDBCUtil.close(null, preparedStatement, connection);
		}
	}

	// {2}删除
	public void deleteUser(int uid) {
		Connection connection = null;
		String sql = "delete from user where uid=?";
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, uid);
			int i = preparedStatement.executeUpdate();
			System.out.println("已删除" + i + "个用户");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}
	}

	// {3}修改
	public void updateUser(User user) {
		Connection connection = null;
		String sql = "update user set uname=?,pwd=?,imgurl=? where uid=?";
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getUname());
			preparedStatement.setString(2, user.getPwd());
			preparedStatement.setString(3, user.getImgurl());
			preparedStatement.setInt(4, user.getUid());
			int i = preparedStatement.executeUpdate();
			System.out.println("已修改" + i + "个用户");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(null, preparedStatement, connection);
		}
	}

	// {4}根据用户名查询
	public User queryByName(String sname) {
		User user = null;
		// 1. 获取MySQL连接
		Connection connection = null;
		// 2. 编写sql语句
		String sql = "select * from user u where u.uname=?";
		PreparedStatement preparedStatement = null;
		ResultSet set = null;
		// 3. 使用Connection创建 PreparedStatement 对象
		try {
			connection = JDBCUtil.getConnection();
			// 4. 执行SQL语句
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, sname);// 给?号赋值
			// 得到结果集
			set = preparedStatement.executeQuery();
			// 5. 遍历该结果集
			while (set.next()) {
				int uid = set.getInt("uid");
				String uname = set.getString("uname");
				String pwd = set.getString("pwd");
				user = new User(uid, uname, pwd);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6. 关闭资源
			JDBCUtil.close(set, preparedStatement, connection);
		}
		return user;
	}

	// {5}根据ID查询
	public User queryById(int sid) throws SQLException {
		String sql = "select * from user u where u.uid=?";
		User user = JdbcUtils.query(sql, User.class, sid);
		return user;
	}

	// {6}查询所有记录
	public List<User> queryAll() {
		User user = null;
		List<User> list = new Vector();
		Connection connection = null;
		String sql = "select * from user";
		PreparedStatement preparedStatement = null;
		ResultSet set = null;
		try {
			connection = JDBCUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			set = preparedStatement.executeQuery();
			while (set.next()) {
				int uid = set.getInt("uid");
				String uname = set.getString("uname");
				String pwd = set.getString("pwd");
				user = new User(uid, uname, pwd);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(set, preparedStatement, connection);
		}
		return list;
	}

	// {7}根据用户名和密码查询
	public User queryByNameAndPassword(String sname, String spwd) {
		User user = null;
		Connection connection = null;
		String sql = "select * from user where uname=? and pwd=?";
		PreparedStatement preparedStatement = null;
		ResultSet set = null;
		try {
			connection = JDBCUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, sname);
			preparedStatement.setString(2, spwd);
			set = preparedStatement.executeQuery();
			while (set.next()) {
				int uid = set.getInt("uid");
				String uname = set.getString("uname");
				String pwd = set.getString("pwd");
				user = new User(uid, uname, pwd);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(set, preparedStatement, connection);
		}
		return user;
	}
//	// {8}根据商品ID查询商品
//	public List<String> quaryByuIdGoods(User u) throws SQLException {
//		List<String> list = new ArrayList<String>();
//		Connection conn = null;
//		Statement stmt;
//		ResultSet rs = null; // g->c
//		try {
//			conn = DriverManager.getConnection(
//					"jdbc:mysql://localhost:3306/nypro?useSSL=false&serverTimezone=UTC&useSSL=false", "root", "0487");
//			String sql = "select goods.goodId from \r\n" + "wishlist_info as pd \r\n" + "join goods on\r\n"
//					+ "pd.goodId = goods.goodId\r\n" + "where pd.uid = " + u.getUid() + ";";
//			stmt = conn.prepareStatement(sql);
//			rs = stmt.executeQuery(sql);
//			System.out.println(rs);
//			while (rs.next()) {
//				list.add(rs.getString("goodId"));
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			try {
//				conn.close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}
//		return list;
//	}

}
